<?php
import('@.Action.PublicAction');
class StatisticsAction extends Action
{
    function _initialize() 
    {
       if(!isset($_SESSION['user_right'])) {
			$this->redirect('login','Public');
        }
        parent::_initialize();
    }
    	
	function index()
	{	
		exit();
	}
	//班级统计
	function bj(){
		
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
		$sql = 'SELECT DISTINCT ('
        . ' substr('
        . ' bj, 1, 4'
        . ' )'
        . ' ) AS nj'
        . ' FROM `xtu_studentinfo` '
        . ' WHERE 1 =1'
        . ' ORDER BY nj ASC ';
		$nj=$Model->query($sql);
	
		$voList=array();
		$count[]=array('yx'=>'','bj'=>"<font color='red'>总计</font>",'num'=>0,'boy'=>0,'girl'=>0);
		if($nj)foreach($nj as $tmp)
		{
			$sql = 'SELECT C.*,CASE girl WHEN girl THEN girl ELSE "0" END AS girl FROM'
					. ' ('
					. ' SELECT A.*,CASE boy WHEN boy THEN boy ELSE "0" END AS boy FROM'
					. ' ('
					. ' SELECT yx, '
					. ' CASE bj'
					. ' WHEN bj'
					. ' THEN bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS num'
					. ' FROM `xtu_studentinfo` '
					. ' WHERE 1 =1'
					. ' AND substr('
					. ' bj, 1, 4'
					. ' ) ='.$tmp['nj'].''
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' ) A LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE bj'
					. ' WHEN bj'
					. ' THEN bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS boy'
					. ' FROM `xtu_studentinfo` '
					. ' WHERE 1 =1'
					. ' AND substr('
					. ' bj, 1, 4'
					. ' ) ='.$tmp['nj'].''
					. ' AND xb = "男"'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' )'
					. ' B ON A.yx=B.yx AND A.bj=B.bj'
					. ' ) C '
					. ' LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE bj'
					. ' WHEN bj'
					. ' THEN bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS girl'
					. ' FROM `xtu_studentinfo` '
					. ' WHERE 1 =1'
					. ' AND substr('
					. ' bj, 1, 4'
					. ' ) ='.$tmp['nj'].''
					. ' AND xb = "女"'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' ) D'
					. ' ON C.yx=D.yx AND C.bj=D.bj '; 
			$list=$Model->query($sql);
			$list[count($list)-1]['bj']="<font color='red'>".$list[count($list)-1]['bj']."</font>";
			$count[0]['num']=$count[0]['num']+$list[count($list)-1]['num'];
			$count[0]['boy']=$count[0]['boy']+$list[count($list)-1]['boy'];
			$count[0]['girl']=$count[0]['girl']+$list[count($list)-1]['girl'];
			$voList=array_merge($voList,$list);
		}
		$voList=array_merge($voList,$count);
		$this->assign('list',$voList);
		$this->display();	
	}	
	
	//资助信息统计
	function zizhu(){
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
		$sql = 'SELECT name,count(*) as num,sum(money) as money,time FROM `xtu_zizhu` group by name ';
		$voList=$Model->query($sql);
		$this->assign('list',$voList);
		$this->display();	
	}	

	//毕业统计
	function graduate(){
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表	
		$sql = "SELECT case quxiang when quxiang then quxiang else '总数' end qx,count( * ) as num FROM `xtu_graduate` "
			. ' WHERE 1 =1'
			. ' GROUP BY quxiang'
			. ' WITH ROLLUP LIMIT 0, 30'; 
		$voList=$Model->query($sql);
		$voList[count($voList)-1]['qx']="<font color='red'>总数</font>";
		$this->assign('list',$voList);
		$this->display();	
	}

	//贷款信息统计
	function loan(){
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
		$sql = 'SELECT pici,count(*) as num, sum(money) as money FROM `xtu_loan` group by pici ';
		$voList=$Model->query($sql);
		$this->assign('list',$voList);
		$this->display();	
	}	
	
	
	//家庭信息统计
	function family(){
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
		$sql = 'SELECT DISTINCT ('
        . ' substr('
        . ' bj, 1, 4'
        . ' )'
        . ' ) AS nj'
        . ' FROM `xtu_studentinfo` '
        . ' WHERE 1 =1'
        . ' ORDER BY nj ASC ';
		$nj=$Model->query($sql);
	
		$voList=array();
		$count[]=array('yx'=>'','bj'=>"<font color='red'>总计</font>",'num'=>0,'yiban'=>0,'teshu'=>0);
		if($nj)foreach($nj as $tmp)
		{
			$sql = 'SELECT C.*,CASE teshu WHEN teshu THEN teshu ELSE "0" END AS teshu FROM'
					. ' ('
					. ' SELECT A.*,CASE yiban WHEN yiban THEN yiban ELSE "0" END AS yiban FROM'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS num'
					. ' FROM `xtu_studentinfo` , `xtu_family` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].''
					. ' AND xtu_studentinfo.xh = xtu_family.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' )A LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS yiban'
					. ' FROM `xtu_studentinfo` , `xtu_family` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].' AND xtu_family.rank="一般困难"'
					. ' AND xtu_studentinfo.xh = xtu_family.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' )B ON A.yx=B.yx AND A.bj=B.bj'
					. ' )C '
					. ' LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS teshu'
					. ' FROM `xtu_studentinfo` , `xtu_family` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].' AND xtu_family.rank="特殊困难"'
					. ' AND xtu_studentinfo.xh = xtu_family.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' ) D'
					. ' ON C.yx=D.yx AND C.bj=D.bj'; 
				
			$list=$Model->query($sql);
			$list[count($list)-1]['bj']="<font color='red'>".$list[count($list)-1]['bj']."</font>";
			$count[0]['num']=$count[0]['num']+$list[count($list)-1]['num'];
			$count[0]['yiban']=$count[0]['yiban']+$list[count($list)-1]['yiban'];
			$count[0]['teshu']=$count[0]['teshu']+$list[count($list)-1]['teshu'];
			$voList=array_merge($voList,$list);
		}
		$voList=array_merge($voList,$count);
		$this->assign('list',$voList);
		$this->display();
	}	
	//党务信息统计
	function party(){
		$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
		$sql = 'SELECT DISTINCT ('
        . ' substr('
        . ' bj, 1, 4'
        . ' )'
        . ' ) AS nj'
        . ' FROM `xtu_studentinfo` '
        . ' WHERE 1 =1'
        . ' ORDER BY nj ASC ';
		$nj=$Model->query($sql);
	
		$voList=array();
		$count[]=array('yx'=>'','bj'=>"<font color='red'>总计</font>",'num'=>0,'num1'=>0,'num2'=>0);
		if($nj)foreach($nj as $tmp)
		{
			$sql = 'SELECT C.*,CASE num2 WHEN num2 THEN num2 ELSE "0" END AS num2 FROM'
					. ' ('
					. ' SELECT A.*,CASE num1 WHEN num1 THEN num1 ELSE "0" END AS num1 FROM'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS num'
					. ' FROM `xtu_studentinfo` , `xtu_party` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].' AND xtu_party.YBTime!=""'
					. ' AND xtu_studentinfo.xh = xtu_party.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' )A LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS num1'
					. ' FROM `xtu_studentinfo` , `xtu_party` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].' AND xtu_party.YBTime!="" AND xtu_party.ZZTime=""'
					. ' AND xtu_studentinfo.xh = xtu_party.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' )B ON A.yx=B.yx AND A.bj=B.bj'
					. ' )C '
					. ' LEFT JOIN'
					. ' ('
					. ' SELECT yx, '
					. ' CASE xtu_studentinfo.bj'
					. ' WHEN xtu_studentinfo.bj'
					. ' THEN xtu_studentinfo.bj'
					. ' ELSE "'.$tmp['nj'].'级小计"'
					. ' END bj, count( * ) AS num2'
					. ' FROM `xtu_studentinfo` , `xtu_party` '
					. ' WHERE substr('
					. ' xtu_studentinfo.bj, 1, 4'
					. ' ) ='.$tmp['nj'].' AND xtu_party.YBTime!="" AND xtu_party.ZZTime!=""'
					. ' AND xtu_studentinfo.xh = xtu_party.xh'
					. ' GROUP BY bj'
					. ' WITH ROLLUP'
					. ' ) D'
					. ' ON C.yx=D.yx AND C.bj=D.bj'; 
				
			$list=$Model->query($sql);
			$list[count($list)-1]['bj']="<font color='red'>".$list[count($list)-1]['bj']."</font>";
			$count[0]['num']=$count[0]['num']+$list[count($list)-1]['num'];
			$count[0]['num1']=$count[0]['num1']+$list[count($list)-1]['num1'];
			$count[0]['num2']=$count[0]['num2']+$list[count($list)-1]['num2'];
			$voList=array_merge($voList,$list);
		}
		$voList=array_merge($voList,$count);
		$this->assign('list',$voList);
		$this->display();
	}			
}
?>